import xlwt
import xlrd
from snownlp import SnowNLP

def caculate(number):
    data = xlrd.open_workbook('data/{}.xls'.format(number))
    table = data.sheets()[0]
    comments = table.col_values(0)  # 读表格的第一列（评论）
    times = table.col_values(1)  # 第二列（时间）

    # 计算情绪值
    sums = []
    for comment in comments:
        sums.append(SnowNLP(comment).sentiments)
    sum = 0
    for each in sums:
        sum += float(each)
    ave = sum / len(comments)  # 计算平均值

    # 写入新表格
    w = xlwt.Workbook()
    s = w.add_sheet('s1')
    for i in range(0, len(comments)):
        s.write(i, 0, comments[i])
        s.write(i, 1, times[i])
        s.write(i, 2, sums[i])
    s.write(len(comments), 1, '平均')
    s.write(len(comments), 2, ave)
    s = 'data/1.xls'
    w.save(s)  # 存储到本地
    return ave
